﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using Tools;
using Tour.DAL;

namespace Tour.BLL
{
    /// <summary>
    /// Summary description for TblCustomer
    /// </summary>
    public class TblCustomerManagerBase
    {
        public TblCustomerManagerBase()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        public int InsertUpdate(TblCustomerEntity item)
        {
            List<SqlParameter> ls = new List<SqlParameter>();
            SqlParameter sp = new SqlParameter("@Output", 1);
            sp.Direction = System.Data.ParameterDirection.Output;
            ls.Add(sp);
            ls.Add(new SqlParameter("@Id", item.Id));
            ls.Add(new SqlParameter("@Name", item.Name));
            ls.Add(new SqlParameter("@BodyCard", item.BodyCard));
            ls.Add(new SqlParameter("@Address", item.Address));
            ls.Add(new SqlParameter("@Phone", item.Phone));
            ls.Add(new SqlParameter("@Email", item.Email));
            ls.Add(new SqlParameter("@BirthDay", item.BirthDay));
            ls.Add(new SqlParameter("@BirthPlace", item.BirthPlace));
            ls.Add(new SqlParameter("@Visa", item.Visa));
            ls.Add(new SqlParameter("@Guarantee", item.Guarantee));
            ls.Add(new SqlParameter("@Entrydate", item.Entrydate));
            ls.Add(new SqlParameter("@ResidenceDateFrom", item.ResidenceDateFrom));
            ls.Add(new SqlParameter("@ResidenceDateTo", item.ResidenceDateTo));
            ls.Add(new SqlParameter("@Gender", item.Gender));
            ls.Add(new SqlParameter("@Note", item.Note));
            ls.Add(new SqlParameter("@CreatedBy", item.CreatedBy));
            ls.Add(new SqlParameter("@CreatedDate", item.CreatedDate));
            SqlParameter[] sqlparam = ls.ToArray();
            SqlHelper.ExecuteNonQuery(SqlHelper.TourConnectString, CommandType.StoredProcedure, "tr_InsertUpdateTblCustomer", sqlparam);
            return int.Parse(sqlparam[0].Value.ToString());
        }
        public int InsertUpdate(int Id, string Name, string BodyCard, string Address, string Phone, string Email, System.DateTime BirthDay, string BirthPlace, string Visa, string Guarantee, System.DateTime Entrydate, System.DateTime ResidenceDateFrom, System.DateTime ResidenceDateTo, bool Gender, string Note, string CreatedBy, System.DateTime CreatedDate)
        {
            List<SqlParameter> ls = new List<SqlParameter>();
            SqlParameter sp = new SqlParameter("@Output", 1);
            sp.Direction = System.Data.ParameterDirection.Output;
            ls.Add(sp);
            ls.Add(new SqlParameter("@Id", Id));
            ls.Add(new SqlParameter("@Name", Name));
            ls.Add(new SqlParameter("@BodyCard", BodyCard));
            ls.Add(new SqlParameter("@Address", Address));
            ls.Add(new SqlParameter("@Phone", Phone));
            ls.Add(new SqlParameter("@Email", Email));
            ls.Add(new SqlParameter("@BirthDay", BirthDay));
            ls.Add(new SqlParameter("@BirthPlace", BirthPlace));
            ls.Add(new SqlParameter("@Visa", Visa));
            ls.Add(new SqlParameter("@Guarantee", Guarantee));
            ls.Add(new SqlParameter("@Entrydate", Entrydate));
            ls.Add(new SqlParameter("@ResidenceDateFrom", ResidenceDateFrom));
            ls.Add(new SqlParameter("@ResidenceDateTo", ResidenceDateTo));
            ls.Add(new SqlParameter("@Gender", Gender));
            ls.Add(new SqlParameter("@Note", Note));
            ls.Add(new SqlParameter("@CreatedBy", CreatedBy));
            ls.Add(new SqlParameter("@CreatedDate", CreatedDate));
            SqlParameter[] sqlparam = ls.ToArray();
            SqlHelper.ExecuteNonQuery(SqlHelper.TourConnectString, CommandType.StoredProcedure, "tr_InsertUpdateTblCustomer", sqlparam);
            return int.Parse(sqlparam[0].Value.ToString());
        }
        public void Delete(int Id)
        {
            List<SqlParameter> ls = new List<SqlParameter>();
            ls.Add(new SqlParameter("@Id", Id));
            SqlParameter[] sqlparam = ls.ToArray();
            SqlHelper.ExecuteNonQuery(SqlHelper.TourConnectString, CommandType.StoredProcedure, "tr_DeleteTblCustomer", sqlparam);
        }
        public TblCustomerEntity GetTblCustomerEntity(DataRow dr)
        {
            TblCustomerEntity item = new TblCustomerEntity();            
            if (dr.Table.Columns.Contains("Id"))
                item.Id = dr["Id"].ToInt();              
            if (dr.Table.Columns.Contains("Name"))
                item.Name = dr["Name"].ToString();              
            if (dr.Table.Columns.Contains("BodyCard"))
                item.BodyCard = dr["BodyCard"].ToString();              
            if (dr.Table.Columns.Contains("Address"))
                item.Address = dr["Address"].ToString();              
            if (dr.Table.Columns.Contains("Phone"))
                item.Phone = dr["Phone"].ToString();              
            if (dr.Table.Columns.Contains("Email"))
                item.Email = dr["Email"].ToString();              
            if (dr.Table.Columns.Contains("BirthDay"))
                item.BirthDay = dr["BirthDay"].ToDateTime();              
            if (dr.Table.Columns.Contains("BirthPlace"))
                item.BirthPlace = dr["BirthPlace"].ToString();              
            if (dr.Table.Columns.Contains("Visa"))
                item.Visa = dr["Visa"].ToString();              
            if (dr.Table.Columns.Contains("Guarantee"))
                item.Guarantee = dr["Guarantee"].ToString();              
            if (dr.Table.Columns.Contains("Entrydate"))
                item.Entrydate = dr["Entrydate"].ToDateTime();              
            if (dr.Table.Columns.Contains("ResidenceDateFrom"))
                item.ResidenceDateFrom = dr["ResidenceDateFrom"].ToDateTime();              
            if (dr.Table.Columns.Contains("ResidenceDateTo"))
                item.ResidenceDateTo = dr["ResidenceDateTo"].ToDateTime();              
            if (dr.Table.Columns.Contains("Gender"))
                item.Gender = dr["Gender"].ToBoolean();              
            if (dr.Table.Columns.Contains("Note"))
                item.Note = dr["Note"].ToString();              
            if (dr.Table.Columns.Contains("CreatedBy"))
                item.CreatedBy = dr["CreatedBy"].ToString();
            if (dr.Table.Columns.Contains("CreatedDate"))
                // item.CreatedDate = dr["CreatedDate"].ToDateTime();  
                item.CreatedDate = dr["CreatedDate"].ToString().ToDateTime("vi-VN");
            return item;
        }        
        public DataTable SelectOneRDT(int Id)
        {
            List<SqlParameter> ls = new List<SqlParameter>();
            ls.Add(new SqlParameter("@Id", Id));
            SqlParameter[] sqlparam = ls.ToArray();
            return SqlHelper.ExecuteDataTable(SqlHelper.TourConnectString, CommandType.StoredProcedure, "tr_SelectTblCustomer", sqlparam);
        }
        public TblCustomerEntity SelectOne(int Id)
        {
            List<SqlParameter> ls = new List<SqlParameter>();
            ls.Add(new SqlParameter("@Id", Id));
            SqlParameter[] sqlparam = ls.ToArray();
            DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.TourConnectString, CommandType.StoredProcedure, "tr_SelectTblCustomer", sqlparam);
            if(dt!=null && dt.Rows.Count>0)
                return GetTblCustomerEntity(dt.Rows[0]);
            return null;
        }
        public DataTable SelectAllRDT()
        {
            return SqlHelper.ExecuteDataTable(SqlHelper.TourConnectString, CommandType.StoredProcedure, "tr_SelectTblCustomersAll", null);
        }
        public List<TblCustomerEntity> SelectAll()
        {
            List<TblCustomerEntity> lsresult= new List<TblCustomerEntity>();
            DataTable dt =  SqlHelper.ExecuteDataTable(SqlHelper.TourConnectString, CommandType.StoredProcedure, "tr_SelectTblCustomersAll", null);
            if (dt != null && dt.Rows.Count > 0)
                for (int i = 0; i < dt.Rows.Count; i++)
                    lsresult.Add(GetTblCustomerEntity(dt.Rows[i]));            
            return lsresult;
        }
    }
}

